{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Retail Data Example\n",
    "\n",
    "Below is a demo applying automated feature engineering to a retail dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import featuretools as ft\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Prepare data\n",
    "\n",
    "We load this data into from a CSV file hosted on Amazon S3. The origial dataset is available for download [here](http://archive.ics.uci.edu/ml/datasets/online+retail)\n",
    "\n",
    "We then break the file up into several entities\n",
    "\n",
    "* **item_purchases**: items in each invoice\n",
    "* **items**: items and associated descriptions\n",
    "* **invoices**: invoices placed \n",
    "* **customers**: customers who placed invoices"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "es = ft.EntitySet(\"retail\")\n",
    "data = pd.read_csv(\"s3://featuretools-static/uk_online_retail.csv\")\n",
    "es.entity_from_dataframe(\"item_purchases\",\n",
    "                   dataframe=data,\n",
    "                   index=\"item_purchase_id\",\n",
    "                   make_index=True,\n",
    "                   time_index=\"InvoiceDate\")\n",
    "\n",
    "es.normalize_entity(new_entity_id=\"items\",\n",
    "                    base_entity_id=\"item_purchases\",\n",
    "                    index=\"StockCode\",\n",
    "                    additional_variables=[\"Description\"])\n",
    "\n",
    "es.normalize_entity(new_entity_id=\"invoices\",\n",
    "                    base_entity_id=\"item_purchases\",\n",
    "                    index=\"InvoiceNo\",\n",
    "                    additional_variables=[\"CustomerID\",\"Country\"])\n",
    "\n",
    "es.normalize_entity(new_entity_id=\"customers\",\n",
    "                    base_entity_id=\"invoices\",\n",
    "                    index=\"CustomerID\",\n",
    "                    additional_variables=[\"Country\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Run Deep Feature Synthesis\n",
    "\n",
    "The input to DFS is a set of entities and a list of relationships (defined by our EntitySet) and the \"target_entity\" to calculate features for. We can supply \"cutoff times\" to specify that we want to calculate features one year after a customer's first invoice.\n",
    "\n",
    "The ouput of DFS is a feature matrix and the corresponding list of feature defintions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>instance_id</th>\n",
       "      <th>time</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CustomerID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>17850.0</th>\n",
       "      <td>17850.0</td>\n",
       "      <td>2011-12-01 08:26:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13047.0</th>\n",
       "      <td>13047.0</td>\n",
       "      <td>2011-12-01 08:34:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12583.0</th>\n",
       "      <td>12583.0</td>\n",
       "      <td>2011-12-01 08:45:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            instance_id                time\n",
       "CustomerID                                 \n",
       "17850.0         17850.0 2011-12-01 08:26:00\n",
       "13047.0         13047.0 2011-12-01 08:34:00\n",
       "12583.0         12583.0 2011-12-01 08:45:00"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cutoff_times = es[\"customers\"].df[[\"CustomerID\", \"first_invoices_time\"]].rename(columns={\"CustomerID\": \"instance_id\", \"first_invoices_time\": \"time\"})\n",
    "cutoff_times[\"time\"] = cutoff_times[\"time\"] + pd.Timedelta(\"365 days\")\n",
    "cutoff_times.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "calulate_feature_matrix: 100%|██████████| 101/101 [00:44<00:00,  2.89it/s]\n"
     ]
    }
   ],
   "source": [
    "from featuretools.primitives import AvgTimeBetween, Mean, Sum, Count, Day\n",
    "\n",
    "feature_matrix, features = ft.dfs(entityset=es, target_entity=\"customers\",\n",
    "                                  cutoff_time=cutoff_times.sample(100),\n",
    "                                  agg_primitives=[AvgTimeBetween, Mean, Sum, Count],\n",
    "                                  trans_primitives=[Day], max_depth=5, verbose=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>DAY(first_invoices_time)</th>\n",
       "      <th>Country</th>\n",
       "      <th>COUNT(invoices)</th>\n",
       "      <th>AVG_TIME_BETWEEN(item_purchases)</th>\n",
       "      <th>AVG_TIME_BETWEEN(invoices)</th>\n",
       "      <th>MEAN(item_purchases.Quantity)</th>\n",
       "      <th>COUNT(item_purchases)</th>\n",
       "      <th>MEAN(item_purchases.UnitPrice)</th>\n",
       "      <th>SUM(item_purchases.UnitPrice)</th>\n",
       "      <th>SUM(item_purchases.Quantity)</th>\n",
       "      <th>...</th>\n",
       "      <th>MEAN(invoices.MEAN(item_purchases.UnitPrice))</th>\n",
       "      <th>MEAN(invoices.AVG_TIME_BETWEEN(item_purchases))</th>\n",
       "      <th>MEAN(invoices.MEAN(item_purchases.Quantity))</th>\n",
       "      <th>MEAN(invoices.COUNT(item_purchases))</th>\n",
       "      <th>MEAN(item_purchases.items.AVG_TIME_BETWEEN(item_purchases))</th>\n",
       "      <th>MEAN(item_purchases.items.COUNT(item_purchases))</th>\n",
       "      <th>MEAN(item_purchases.items.MEAN(item_purchases.UnitPrice))</th>\n",
       "      <th>MEAN(item_purchases.items.MEAN(item_purchases.Quantity))</th>\n",
       "      <th>MEAN(item_purchases.items.SUM(item_purchases.Quantity))</th>\n",
       "      <th>MEAN(item_purchases.items.SUM(item_purchases.UnitPrice))</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CustomerID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>15812.0</th>\n",
       "      <td>26</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>5</td>\n",
       "      <td>453790.500000</td>\n",
       "      <td>4537905.0</td>\n",
       "      <td>22.487805</td>\n",
       "      <td>41</td>\n",
       "      <td>5.051707</td>\n",
       "      <td>207.12</td>\n",
       "      <td>922</td>\n",
       "      <td>...</td>\n",
       "      <td>3.819674</td>\n",
       "      <td>0.0</td>\n",
       "      <td>48.085556</td>\n",
       "      <td>8.2</td>\n",
       "      <td>197200.908471</td>\n",
       "      <td>610.219512</td>\n",
       "      <td>5.537463</td>\n",
       "      <td>11.880670</td>\n",
       "      <td>8302.219512</td>\n",
       "      <td>2995.527561</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15353.0</th>\n",
       "      <td>7</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>2</td>\n",
       "      <td>157496.129032</td>\n",
       "      <td>9764760.0</td>\n",
       "      <td>5.444444</td>\n",
       "      <td>63</td>\n",
       "      <td>3.312857</td>\n",
       "      <td>208.71</td>\n",
       "      <td>343</td>\n",
       "      <td>...</td>\n",
       "      <td>3.623382</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.536765</td>\n",
       "      <td>31.5</td>\n",
       "      <td>119518.228805</td>\n",
       "      <td>501.301587</td>\n",
       "      <td>3.891205</td>\n",
       "      <td>8.587544</td>\n",
       "      <td>4695.984127</td>\n",
       "      <td>2468.699206</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17928.0</th>\n",
       "      <td>7</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>2</td>\n",
       "      <td>788697.272727</td>\n",
       "      <td>17351340.0</td>\n",
       "      <td>6.086957</td>\n",
       "      <td>23</td>\n",
       "      <td>2.988696</td>\n",
       "      <td>68.74</td>\n",
       "      <td>140</td>\n",
       "      <td>...</td>\n",
       "      <td>3.156275</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.186275</td>\n",
       "      <td>11.5</td>\n",
       "      <td>146500.008131</td>\n",
       "      <td>371.695652</td>\n",
       "      <td>19.645460</td>\n",
       "      <td>10.163183</td>\n",
       "      <td>4084.217391</td>\n",
       "      <td>10311.004348</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            DAY(first_invoices_time)         Country  COUNT(invoices)  \\\n",
       "CustomerID                                                              \n",
       "15812.0                           26  United Kingdom                5   \n",
       "15353.0                            7  United Kingdom                2   \n",
       "17928.0                            7  United Kingdom                2   \n",
       "\n",
       "            AVG_TIME_BETWEEN(item_purchases)  AVG_TIME_BETWEEN(invoices)  \\\n",
       "CustomerID                                                                 \n",
       "15812.0                        453790.500000                   4537905.0   \n",
       "15353.0                        157496.129032                   9764760.0   \n",
       "17928.0                        788697.272727                  17351340.0   \n",
       "\n",
       "            MEAN(item_purchases.Quantity)  COUNT(item_purchases)  \\\n",
       "CustomerID                                                         \n",
       "15812.0                         22.487805                     41   \n",
       "15353.0                          5.444444                     63   \n",
       "17928.0                          6.086957                     23   \n",
       "\n",
       "            MEAN(item_purchases.UnitPrice)  SUM(item_purchases.UnitPrice)  \\\n",
       "CustomerID                                                                  \n",
       "15812.0                           5.051707                         207.12   \n",
       "15353.0                           3.312857                         208.71   \n",
       "17928.0                           2.988696                          68.74   \n",
       "\n",
       "            SUM(item_purchases.Quantity)  \\\n",
       "CustomerID                                 \n",
       "15812.0                              922   \n",
       "15353.0                              343   \n",
       "17928.0                              140   \n",
       "\n",
       "                                      ...                             \\\n",
       "CustomerID                            ...                              \n",
       "15812.0                               ...                              \n",
       "15353.0                               ...                              \n",
       "17928.0                               ...                              \n",
       "\n",
       "            MEAN(invoices.MEAN(item_purchases.UnitPrice))  \\\n",
       "CustomerID                                                  \n",
       "15812.0                                          3.819674   \n",
       "15353.0                                          3.623382   \n",
       "17928.0                                          3.156275   \n",
       "\n",
       "            MEAN(invoices.AVG_TIME_BETWEEN(item_purchases))  \\\n",
       "CustomerID                                                    \n",
       "15812.0                                                 0.0   \n",
       "15353.0                                                 0.0   \n",
       "17928.0                                                 0.0   \n",
       "\n",
       "            MEAN(invoices.MEAN(item_purchases.Quantity))  \\\n",
       "CustomerID                                                 \n",
       "15812.0                                        48.085556   \n",
       "15353.0                                         7.536765   \n",
       "17928.0                                         9.186275   \n",
       "\n",
       "            MEAN(invoices.COUNT(item_purchases))  \\\n",
       "CustomerID                                         \n",
       "15812.0                                      8.2   \n",
       "15353.0                                     31.5   \n",
       "17928.0                                     11.5   \n",
       "\n",
       "            MEAN(item_purchases.items.AVG_TIME_BETWEEN(item_purchases))  \\\n",
       "CustomerID                                                                \n",
       "15812.0                                         197200.908471             \n",
       "15353.0                                         119518.228805             \n",
       "17928.0                                         146500.008131             \n",
       "\n",
       "            MEAN(item_purchases.items.COUNT(item_purchases))  \\\n",
       "CustomerID                                                     \n",
       "15812.0                                           610.219512   \n",
       "15353.0                                           501.301587   \n",
       "17928.0                                           371.695652   \n",
       "\n",
       "            MEAN(item_purchases.items.MEAN(item_purchases.UnitPrice))  \\\n",
       "CustomerID                                                              \n",
       "15812.0                                              5.537463           \n",
       "15353.0                                              3.891205           \n",
       "17928.0                                             19.645460           \n",
       "\n",
       "            MEAN(item_purchases.items.MEAN(item_purchases.Quantity))  \\\n",
       "CustomerID                                                             \n",
       "15812.0                                             11.880670          \n",
       "15353.0                                              8.587544          \n",
       "17928.0                                             10.163183          \n",
       "\n",
       "            MEAN(item_purchases.items.SUM(item_purchases.Quantity))  \\\n",
       "CustomerID                                                            \n",
       "15812.0                                           8302.219512         \n",
       "15353.0                                           4695.984127         \n",
       "17928.0                                           4084.217391         \n",
       "\n",
       "            MEAN(item_purchases.items.SUM(item_purchases.UnitPrice))  \n",
       "CustomerID                                                            \n",
       "15812.0                                           2995.527561         \n",
       "15353.0                                           2468.699206         \n",
       "17928.0                                          10311.004348         \n",
       "\n",
       "[3 rows x 22 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "feature_matrix.sample(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Feature: DAY(first_invoices_time)>,\n",
       " <Feature: Country>,\n",
       " <Feature: COUNT(invoices)>,\n",
       " <Feature: AVG_TIME_BETWEEN(item_purchases)>,\n",
       " <Feature: AVG_TIME_BETWEEN(invoices)>,\n",
       " <Feature: MEAN(item_purchases.Quantity)>,\n",
       " <Feature: COUNT(item_purchases)>,\n",
       " <Feature: MEAN(item_purchases.UnitPrice)>,\n",
       " <Feature: SUM(item_purchases.UnitPrice)>,\n",
       " <Feature: SUM(item_purchases.Quantity)>,\n",
       " <Feature: MEAN(invoices.SUM(item_purchases.UnitPrice))>,\n",
       " <Feature: MEAN(invoices.SUM(item_purchases.Quantity))>,\n",
       " <Feature: MEAN(invoices.MEAN(item_purchases.UnitPrice))>,\n",
       " <Feature: MEAN(invoices.AVG_TIME_BETWEEN(item_purchases))>,\n",
       " <Feature: MEAN(invoices.MEAN(item_purchases.Quantity))>,\n",
       " <Feature: MEAN(invoices.COUNT(item_purchases))>,\n",
       " <Feature: MEAN(item_purchases.items.AVG_TIME_BETWEEN(item_purchases))>,\n",
       " <Feature: MEAN(item_purchases.items.COUNT(item_purchases))>,\n",
       " <Feature: MEAN(item_purchases.items.MEAN(item_purchases.UnitPrice))>,\n",
       " <Feature: MEAN(item_purchases.items.MEAN(item_purchases.Quantity))>,\n",
       " <Feature: MEAN(item_purchases.items.SUM(item_purchases.Quantity))>,\n",
       " <Feature: MEAN(item_purchases.items.SUM(item_purchases.UnitPrice))>]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "features"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}
